Fixed Database roles should have only authorized users or groups as members.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15151	DM0531-SQLServer9	SV-23769r2_rule	ECLP-1	Medium

Description
Fixed database roles provide a mechanism to grant groups of privileges to users. These privilege groupings are defined by the installation or upgrade of the SQL Server software at the discretion of Microsoft. Memberships in these roles granted to users should be strictly controlled and monitored. Privileges assigned to these roles should be reviewed for change after software upgrade or maintenance to ensure that the privileges continue to be appropriate to the assigned members.

Description

Fixed database roles provide a mechanism to grant groups of privileges to users. These privilege groupings are defined by the installation or upgrade of the SQL Server software at the discretion of Microsoft. Memberships in these roles granted to users should be strictly controlled and monitored. Privileges assigned to these roles should be reviewed for change after software upgrade or maintenance to ensure that the privileges continue to be appropriate to the assigned members.

STIG	Date
Microsoft SQL Server 2005 Database Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-22831r2_chk )
From the query prompt: SELECT name AS [Database Name] FROM [master].sys.databases WHERE state = 0 Repeat for each database: From the query prompt: USE [Database Name] SELECT u.name AS [Principal], g.name AS [Role] FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g WHERE r.role_principal_id = g.principal_id AND r.member_principal_id = u.principal_id AND g.is_fixed_role = 1 ORDER BY u.name, g.name The DBO membership in the db_owner fixed database role does not require explicit authorization and is Not a Finding. Verify fixed database role assignments for each Principal listed in the System Security Plan. If any Principals are not authorized, this is a Finding. Any fixed database role assignments to the Guest Principal is a finding.

Check Text ( C-22831r2_chk )

From the query prompt:

SELECT name AS [Database Name]
FROM [master].sys.databases
WHERE state = 0

Repeat for each database:

From the query prompt:

USE [Database Name]
SELECT u.name AS [Principal], g.name AS [Role]
FROM sys.database_role_members r, sys.database_principals u, sys.database_principals g
WHERE r.role_principal_id = g.principal_id
AND r.member_principal_id = u.principal_id
AND g.is_fixed_role = 1
ORDER BY u.name, g.name

The DBO membership in the db_owner fixed database role does not require explicit authorization and is Not a Finding.

Verify fixed database role assignments for each Principal listed in the System Security Plan. If any Principals are not authorized, this is a Finding.

Any fixed database role assignments to the Guest Principal is a finding.

Fix Text (F-19750r1_fix)
Grant fixed database roles only to authorized accounts. Revoke unauthorized accounts from fixed database roles. From the SQL Server Management Studio GUI: To deassign roles: 1. Expand [instance name] 2. Expand Databases 3. Expand [database type] 4. Expand [database name] 5. Expand Security 6. Expand Roles 7. Expand Database Roles 8. Double-click the role to be removed from the assigned user 9. Select the user's account under Role Members 10. Click on the Remove button Document fixed database role grants in the System Security Plan and authorize with the IAO.

Fix Text (F-19750r1_fix)

Grant fixed database roles only to authorized accounts.

Revoke unauthorized accounts from fixed database roles.

From the SQL Server Management Studio GUI:

To deassign roles:
1. Expand [instance name]
2. Expand Databases
3. Expand [database type]
4. Expand [database name]
5. Expand Security
6. Expand Roles
7. Expand Database Roles
8. Double-click the role to be removed from the assigned user
9. Select the user's account under Role Members
10. Click on the Remove button

Document fixed database role grants in the System Security Plan and authorize with the IAO.